﻿Imports System.Data
Imports System.Data.SqlClient
Imports System.Windows.Forms.Form
'Imports Microsoft.Office.Interop
Imports System.IO
Module HamXuLy
    '<<<<Lưu hồ sơ scan>>>>
    Public Sub StorePicture(sohoso As Integer, filename As String)
        Dim imageData As Byte() = Nothing
        Using fs As New FileStream(filename, FileMode.Open, FileAccess.Read)
            imageData = New Byte(fs.Length - 1) {}
            fs.Read(imageData, 0, CInt(fs.Length))
        End Using
        Dim cmd As New SqlCommand("InsertImageCG", ob_cnn)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.AddWithValue("@sohoso", sohoso)
        cmd.Parameters("@sohoso").Direction = ParameterDirection.Input
        cmd.Parameters.AddWithValue("@tenname", filename.Split("\\").Last())
        cmd.Parameters("@tenname").Direction = ParameterDirection.Input
        cmd.Parameters.Add("@hinhanh", SqlDbType.Image)
        cmd.Parameters("@hinhanh").Direction = ParameterDirection.Input
        cmd.Parameters("@hinhanh").Value = imageData
        ob_cnn.Open()
        cmd.ExecuteNonQuery()
        ob_cnn.Close()
    End Sub
    Public Function RetrieveImage(tenfile As String, sohoso As Integer) As Byte()
        ob_cnn.Open()
        Dim cmd As New SqlCommand("select hinhanh from LuuHinhTam where tenfile = '" & tenfile & "' and sohoso = " & sohoso & "", ob_cnn)
        Dim imageData As Byte() = DirectCast(cmd.ExecuteScalar(), Byte())
        If Not imageData Is Nothing Then
            Using ms As New MemoryStream(imageData, 0, imageData.Length)
                ms.Write(imageData, 0, imageData.Length)
            End Using
        End If
        ob_cnn.Close()
        Return imageData
    End Function
    Public Function LoadVaoGridview(sohoso As Integer) As DataTable
        ob_cnn.Open()
        Dim cmd As New SqlCommand("select * from LuuHinhTam where sohoso = @sohoso", ob_cnn)
        cmd.Parameters.AddWithValue("@sohoso", sohoso)
        Dim dt As New DataTable()
        dt.Load(cmd.ExecuteReader())
        ob_cnn.Close()
        Return dt
    End Function
    Public Function KiemTraFile(sohoso As Integer, tenFile As String) As Boolean

        Dim cmd As New SqlCommand("select * from LuuHinhTam where sohoso = @sohoso and TenFile = @tenfile", ob_cnn)
        cmd.Parameters.AddWithValue("@sohoso", sohoso)
        cmd.Parameters.AddWithValue("@tenfile", tenFile)
        Dim dt As New DataTable()
        ob_cnn.Open()
        dt.Load(cmd.ExecuteReader())
        If dt.Rows.Count > 0 Then
            ob_cnn.Close()
            Return True
        End If
        ob_cnn.Close()
        Return False
    End Function
    '<<<< Kết thúc Lưu hồ sơ scan>>>>
    Public Function KiemTraKyTu(ByVal Str As String)
        For Each kytu As Char In Str
            If IsNumeric(kytu) = False Then
                Return False
            End If
        Next
        Return True
    End Function
    Public Function KiemTraSoAm(ByVal Str As String)
        If Str.StartsWith("-") Or Str.Contains(",") Then
            Return False
        End If
        Return True
    End Function
    Public Function LoadThongTin(sql As String) As DataTable
        Dim cmd As SqlCommand
        Dim da As SqlDataAdapter
        Dim tb As DataTable

        tb = New DataTable
        Try
            cmd = New SqlCommand(sql, ob_cnn)
            da = New SqlDataAdapter(cmd)
            da.Fill(tb)
        Catch ex As Exception
            MessageBox.Show("Lỗi. Sai thông tin ", "", MessageBoxButtons.OK, MessageBoxIcon.Error)
            ob_cnn.Close()
        End Try

        Return tb
    End Function
    Public Sub XuatExcelSTD()
        Dim gr As SaveFileDialog = New SaveFileDialog()
        Dim FileName As String
        With gr
            .InitialDirectory = "D:\"
            .Filter = "Microsoft Excel Worksheet (*.xls)|*.xls"
            .FilterIndex = 1
        End With
        If gr.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            FileName = gr.FileName
        Else
            Exit Sub
        End If
        'Extracting from database
        '  Dim col, row As Integer
        Dim Excel As Object = CreateObject("Excel.Application")

        If Excel Is Nothing Then
            MsgBox("m***cel re..", MsgBoxStyle.Critical)
            Return
        End If
        'Export to Excel process


        Try
            With Excel
                '.SheetsInNewWorkbook = 1
                .Workbooks.Open(Application.StartupPath & "\Template\MAU_STD.dll")
                .Worksheets(1).Select()
                .Cells.Font.Size = 12
                .Cells.Font.Name = "Times New Roman"
                Dim i As Integer
                Dim j As Integer
                Dim sql As String = "SELECT chusd1, ngaybd, tobd, sothua1, noidungbd FROM sobiendong "
                ' SqlAdapter
                Dim dscmd As New SqlDataAdapter(sql, ob_cnn)
                ' DataSet
                Dim ds As New DataSet
                dscmd.Fill(ds)

                For i = 0 To ds.Tables(0).Rows.Count - 1
                    'Column
                    For j = 0 To ds.Tables(0).Columns.Count - 1
                        ' this i change to header line cells 
                        .Cells(i + 5, j + 2) = ds.Tables(0).Rows(i).Item(j)
                    Next
                    .Cells(i + 5, 1) = i + 1
                Next
                .ActiveCell.Worksheet.SaveAs(FileName)
            End With
            System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
            Excel = Nothing
            MsgBox("Dữ liệu đã xuất sang Excel thành công!", MsgBoxStyle.Information)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub
    Public Sub LoadHuyen(ByRef cmb As ComboBox)
        Dim sql As String
        sql = "Select * From TENHUYEN"

        cmb.ValueMember = "MaH"
        cmb.DisplayMember = "T_Huyen"
        cmb.DataSource = LoadThongTin(sql)

    End Sub
    Public Sub LoadTenVanBan(ByRef cmb As ComboBox)
        Dim sql As String
        sql = "Select * From DMTenVanBan  order by TenVanBan"

        cmb.ValueMember = "MaTenVanBan"
        cmb.DisplayMember = "TenVanBan"
        cmb.DataSource = LoadThongTin(sql)

    End Sub
    Public Sub LoadXa(_MaHuyen As Integer, ByRef cmb As ComboBox)
        Dim sql As String
        sql = "Select * From TENXA Where MaHuyen = " & _MaHuyen & " "

        cmb.ValueMember = "MaXa"
        cmb.DisplayMember = "TenXA"
        cmb.DataSource = LoadThongTin(sql)

    End Sub
    Public Sub LoadPhong(ByRef cmb As ComboBox)
        Dim sql As String
        sql = "Select * From DMPHONG"

        cmb.ValueMember = "MaPhongID"
        cmb.DisplayMember = "TenPhong"
        cmb.DataSource = LoadThongTin(sql)

    End Sub

    Public Sub LoadKe(ByRef cmb As ComboBox)
        Dim sql As String
        sql = "Select * From DMKE"

        cmb.ValueMember = "MaKeID"
        cmb.DisplayMember = "TenKe"
        cmb.DataSource = LoadThongTin(sql)

    End Sub

    Public Sub LoadKho(ByRef cmb As ComboBox)
        Dim sql As String
        sql = "Select * From DMKHO"

        cmb.ValueMember = "MaKhoID"
        cmb.DisplayMember = "TenKho"
        cmb.DataSource = LoadThongTin(sql)

    End Sub

    Public Sub LoadTinhTrang(ByRef cmb As ComboBox)
        Dim sql As String
        sql = "Select * From DMTINHTRANG"

        cmb.ValueMember = "MaTinhTrangID"
        cmb.DisplayMember = "TenTinhTrang"
        cmb.DataSource = LoadThongTin(sql)

    End Sub
    Public Sub LoadHinhThuc(ByRef cmb As ComboBox)
        Dim sql As String
        sql = "Select * From DMHINHTHUC"

        cmb.ValueMember = "MaHinhThucID"
        cmb.DisplayMember = "TenHinhThuc"
        cmb.DataSource = LoadThongTin(sql)

    End Sub
    Public Sub LoadNguongoc(ByRef cmb As ComboBox)
        Dim sql As String
        sql = "Select * From LoaiNguonGocSuDung"

        cmb.ValueMember = "MaNguongocSudungdat"
        cmb.DisplayMember = "TenNguongocSudungdat"
        cmb.DataSource = LoadThongTin(sql)

    End Sub

    Public Function AutoID(strTable As String) As Integer
        Dim sql = "Select IDENT_CURRENT('" & strTable & "' )"
        Dim tb As New DataTable
        tb = LoadThongTin(sql)

        Return Integer.Parse(tb.Rows(0)(0).ToString)

    End Function


    'Thao tac du lieu = update, insert, delete
    Public Function ThaoTacDuLieu(sql As String) As Boolean
        Dim cmd As SqlCommand
        Try
            If (ob_cnn.State = ConnectionState.Closed) Then
                ob_cnn.Open()
            End If

            cmd = New SqlCommand(sql, ob_cnn)
            cmd.ExecuteNonQuery()
            ob_cnn.Close()
            Return True
        Catch ex As Exception
            MessageBox.Show("Thao tác không hợp lệ")
            ob_cnn.Close()
            Return False
        End Try



    End Function
    Public Sub XuatExcel(dgv As DataGridView, sheetName As String)
        ' xuat truc tiep ra excel
        Dim gr As SaveFileDialog = New SaveFileDialog()
        Dim FileName As String
        With gr
            .InitialDirectory = "D:\"
            .Filter = "Microsoft Excel Worksheet (*.xls)|*.xls"
            .FilterIndex = 1
        End With
        If gr.ShowDialog = System.Windows.Forms.DialogResult.OK Then
            FileName = gr.FileName
        Else
            Exit Sub
        End If
        'Extracting from database
        Dim col, row As Integer
        Dim Excel As Object = CreateObject("Excel.Application")

        If Excel Is Nothing Then
            MsgBox("m***cel re..", MsgBoxStyle.Critical)
            Return
        End If
        'Export to Excel process
        Try
            With Excel
                .SheetsInNewWorkbook = 1
                .Workbooks.Add()
                .Worksheets(1).Select()
                .Worksheets(1).name = sheetName
                Dim i As Integer = 1
                For col = 0 To dgv.Columns.Count - 1
                    .cells(1, i).value = dgv.Columns(col).HeaderText
                    .cells(1, i).EntireRow.Font.Bold = True
                    i += 1
                Next
                i = 2
                Dim k As Integer = 1
                For col = 0 To dgv.Columns.Count - 1
                    i = 2
                    For row = 0 To dgv.Rows.Count - 1
                        With .worksheets(1)
                            .Cells(i, k).Value = dgv.Rows(row).Cells(col).Value
                            i += 1
                        End With
                    Next
                    k += 1
                Next
                .ActiveCell.Worksheet.SaveAs(FileName)
            End With
            System.Runtime.InteropServices.Marshal.ReleaseComObject(Excel)
            Excel = Nothing
            MsgBox("Dữ liệu xuất ra Excel thành công!", MsgBoxStyle.Information)
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
        ' The excel is created and opened for insert value. We most close this excel using this system
        Dim pro() As Process = System.Diagnostics.Process.GetProcessesByName("EXCEL")
        For Each i As Process In pro
            i.Kill()
        Next
    End Sub
    Public Sub ExportWord(ts() As String, gt() As String, hosocungcap As String, sophieu As String)
        Const CLSO = "Word.Application"
        Const CLSD = "Word.Document"
        Const wdReplaceAll = 2
        Dim WordApp As Object 'Word.Application
        Dim WordDoc As Object 'Word.Document
        Dim Selection As Object 'Word.Application.Selection
        System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor
        Try
            'If WordApp Is Nothing Then
            WordApp = CreateObject(CLSO)
            'End If
            WordApp.Visible = True
            WordDoc = CreateObject(CLSD)
            WordApp.Visible = False
            WordDoc = WordApp.Documents.Open(Application.StartupPath & "\Template\" & hosocungcap & ".dll")

            'Tim kiem va thay the cac bien

            Selection = WordApp.Selection
            Dim i As Integer
            For i = 0 To gt.Length - 1

                Selection.Find.ClearFormatting()
                Selection.Find.Replacement.ClearFormatting()
                With Selection.Find
                    .Text = ts(i) 'Tu can tim
                    .Replacement.Text = gt(i)   'Tu thay the
                    .Forward = True  'Ve phia truoc
                    .MatchCase = True
                    .MatchWholeWord = True 'True
                    .Execute(Replace:=wdReplaceAll)
                End With
            Next
            Dim strNewDirPath As String = "D:\InPhieu"
            If Not System.IO.Directory.Exists(strNewDirPath) Then
                System.IO.Directory.CreateDirectory(strNewDirPath)
            End If
            WordDoc.SaveAs("" & strNewDirPath & "\" & hosocungcap & "_So-" & sophieu & ".doc")
            MessageBox.Show("In phiếu thành công !" & vbCrLf & "Phiếu được lưu tại: " & strNewDirPath & ".")
            WordApp.Visible = True
            WordApp.Activate()
        Catch ex As Exception
            MessageBox.Show("In phiếu bị lỗi: " & ex.Message)
        Finally
            WordApp = Nothing
            System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default
        End Try
    End Sub
    Public Function GetTenNguoiNhap(_tenDangNhap As String) As String
        Dim sql As String
        Try
            sql = "SELECT hotenNguoiDangNhap FROM LOGIN WHERE tenDangNhap like '" & _tenDangNhap & "' "
            Return LoadThongTin(sql).Rows(0)(0).ToString
        Catch
            Return ""
        End Try
    End Function

End Module
